USE Liberty
GO

-- ------------------------------------------------------------------------------------------------
-- Cases Table Create Script
-- ------------------------------------------------------------------------------------------------
SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

PRINT 'Creating Table dbo.Cases...'
GO

IF OBJECT_ID('dbo.Cases', 'U') IS NULL
BEGIN
	CREATE TABLE dbo.Cases
	(OID				bigint		IDENTITY(1, 1)	NOT NULL
	,Version			rowversion
	,ServiceManID		bigint						NOT NULL
	,InjuryDate			datetime						NULL
	,Injury				varchar(128)					NULL
	,CaseVolunteerID	bigint							NULL
	,CaseDirectorID		bigint							NULL

	,UpdatedDate		datetime					NOT NULL
		CONSTRAINT DFLT_Cases_UpdatedDate		DEFAULT GETDATE()
	,CreatedDate		datetime					NOT NULL
		CONSTRAINT DFLT_Cases_CreatedDate		 DEFAULT GETDATE()

	,CONSTRAINT PK_Cases PRIMARY KEY CLUSTERED( OID ) --ON PRIMARY

	,CONSTRAINT FK_Cases_ServiceMen FOREIGN KEY (ServiceManID)
		REFERENCES dbo.ServiceMen(OID)

	,CONSTRAINT FK_Cases_Users1 FOREIGN KEY (CaseVolunteerID)
		REFERENCES dbo.Users(OID)

	,CONSTRAINT FK_Cases_Users2 FOREIGN KEY (CaseDirectorID)
		REFERENCES dbo.Users(OID)

	)

	PRINT 'Table dbo.Cases has been created successfully.'

END
ELSE 
BEGIN
    PRINT 'Table dbo.Cases already exists.'
END

GO

---- If you need to drop the table and recreate it... 
--IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_CaseEvents_Cases')
--BEGIN	ALTER TABLE CaseEvents DROP CONSTRAINT FK_CaseEvents_Cases	END
--
--IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_CaseNotes_Cases')
--BEGIN	ALTER TABLE CaseNotes DROP CONSTRAINT FK_CaseNotes_Cases	END
--
--IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_CaseAssignments_Cases')
--BEGIN	ALTER TABLE CaseAssignments DROP CONSTRAINT FK_CaseAssignments_Cases	END
--
--IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_Visits_Cases')
--BEGIN	ALTER TABLE Visits DROP CONSTRAINT FK_Visits_Cases	END
--
--IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_Grants_Cases')
--BEGIN	ALTER TABLE Grants DROP CONSTRAINT FK_Grants_Cases	END
--
--DROP TABLE dbo.Cases
--
--
---- Then after recreating it
--IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'CaseEvents')
--BEGIN
--	ALTER TABLE CaseEvents ADD CONSTRAINT FK_CaseEvents_Cases FOREIGN KEY (CaseID)
--		REFERENCES DBO.Cases(OID)
--END
--
--IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'CaseNotes')
--BEGIN
--	ALTER TABLE CaseNotes ADD CONSTRAINT FK_CaseNotes_Cases FOREIGN KEY (CaseID)
--		REFERENCES DBO.Cases(OID)
--END
--
--IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'CaseAssignments')
--BEGIN
--	ALTER TABLE CaseAssignments ADD CONSTRAINT FK_CaseAssignments_Cases FOREIGN KEY (CaseID)
--		REFERENCES DBO.Cases(OID)
--END
--
--IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'Visits')
--BEGIN
--	ALTER TABLE Visits ADD CONSTRAINT FK_Visits_Cases FOREIGN KEY (CaseID)
--		REFERENCES DBO.Cases(OID)
--END
--
--IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'Grants')
--BEGIN
--	ALTER TABLE Grants ADD CONSTRAINT FK_Grants_Cases FOREIGN KEY (CaseID)
--		REFERENCES DBO.Cases(OID)
--END
